package group.sicher.core.utils;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import group.sicher.core.exception.ResponseException;
import group.sicher.core.log.LogManagerUtils;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.TimeZone;

import static org.apache.poi.ss.usermodel.DateUtil.isADateFormat;
import static org.apache.poi.ss.usermodel.DateUtil.isInternalDateFormat;


public class ExcelHelper {

    private static final Logger logger = LogManagerUtils.getPlatformLogger();

    public static final String EXCEL_SUFFIX_XLS = ".xls";
    public static final String EXCEL_SUFFIX_XLSX = ".xlsx";
    public static final String EMPTY = "";
    public static final String POINT = ".";
    private static final String DEFAULT_SHEET_NAME = "sheet";
    public static int totalRows; //sheet中总行数
    public static int totalCells; //每一行总单元格数
    public static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
    private static ExcelHelper instance;


    public ExcelHelper() {

    }

    public static ExcelHelper getInstance() {
        if (instance == null) {
            return new ExcelHelper();
        }
        return instance;
    }

    public static void mergeCell(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        if (sheet == null) {
            throw new ResponseException("无效Sheet");
        }
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }

    public static void setCellValue(Sheet sheet, int rowNum, int colNum, Object value) {
        if (sheet == null) {
            throw new ResponseException("无效Sheet");
        }
        Row row = ExcelHelper.getRow(sheet, rowNum);
        ExcelHelper.setCellValue(row.createCell(colNum), value);
    }

    public static Row getRow(Sheet sheet, int rowNum) {
        Row row = sheet.getRow(rowNum);
        if (row == null) {
            row = sheet.createRow(rowNum);
        }
        return row;
    }

    /**
     * 文件内容的写入
     *
     * @param cell
     * @param value
     */
    public static void setCellValue(Cell cell, Object value) {
        if (value instanceof String) {
            String str = value.toString();
            if (str.length() >= 32767) {
                str = str.substring(0, 32764) + "...";
            }
            cell.setCellValue(str);
        } else if (value instanceof Integer) {
            cell.setCellValue((Integer) value);
        } else if (value instanceof Long) {
            cell.setCellValue((Long) value);
        } else if (value instanceof Float) {
            cell.setCellValue((Float) value);
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else if (value instanceof BigDecimal) {
            cell.setCellValue(((BigDecimal) value).doubleValue());
        } else {
            cell.setCellValue("");
        }
    }

    /**
     * Excel 文件读取接口
     *
     * @param input  文件流
     * @param suffix 文件后缀
     * @return
     * @throws IOException
     */
    public static List<Map<String, Object>> readExcel(InputStream input, String suffix) throws IOException {
        if (null == input) {
            return Lists.newArrayList();
        }
        if (StringUtils.isBlank(suffix) || suffix.equals(ExcelHelper.EXCEL_SUFFIX_XLS)) {
            return readXls(input);
        } else {
            return readXlsx(input);
        }
    }

    /**
     * 读取Excel
     *
     * @param input
     * @return
     */
    private static List<Map<String, Object>> readXls(InputStream input) {
        List<Map<String, Object>> list = Lists.newArrayList();
        HSSFWorkbook wb = null;
        Map<String, Object> objectMap = null;
        try {
            // 创建文档
            wb = new HSSFWorkbook(input);
            //读取sheet(页)
            for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++) {
                HSSFSheet hssfSheet = wb.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                totalRows = hssfSheet.getLastRowNum();
                List<String> titles = Lists.newArrayList();
                //获取文件列头
                HSSFRow titlesRow = hssfSheet.getRow(0);
                if (titlesRow != null) {
                    int totalCells = titlesRow.getLastCellNum();
                    for (short c = 0; c <= totalCells + 1; c++) {
                        HSSFCell cell = titlesRow.getCell(c);
                        if (cell == null) {
                            titles.add(ExcelHelper.EMPTY);
                            continue;
                        }
//                        titles.add(ExcelHelper.getHValue(cell).trim());
                        titles.add(ExcelHelper.getHValue(cell).replaceAll("\r|\n", "")
                                .replaceAll(" ", "").trim());
                    }
                }
                //读取Row,从第二行开始
                for (int rowNum = 1; rowNum <= totalRows; rowNum++) {
                    HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow != null) {
                        objectMap = Maps.newHashMap();
                        totalCells = hssfRow.getLastCellNum();
                        //读取列，从第一列开始
                        for (short c = 0; c <= totalCells + 1; c++) {
                            HSSFCell cell = hssfRow.getCell(c);
                            if (cell == null) {
                                objectMap.put(titles.get(c), ExcelHelper.EMPTY);
                                continue;
                            }
                            objectMap.put(titles.get(c), ExcelHelper.getHValue(cell).trim());
                        }
                        objectMap.put("row", rowNum + 1);
                        list.add(objectMap);
                    }
                }
            }
            return list;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                input.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public static List<Map<String, Object>> readXlsx(InputStream input) {
        List<Map<String, Object>> list = Lists.newArrayList();
        XSSFWorkbook wb = null;
        Map<String, Object> objectMap = null;
        try {
            // 创建文档
            wb = new XSSFWorkbook(input);
            //读取sheet(页)
            for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++) {
                XSSFSheet xssfSheet = wb.getSheetAt(numSheet);
                if (xssfSheet == null) {
                    continue;
                }
                totalRows = xssfSheet.getLastRowNum();
                List<String> titles = Lists.newArrayList();
                //获取文件列头
                XSSFRow titlesRow = xssfSheet.getRow(0);
                if (titlesRow != null) {
                    int totalCells = titlesRow.getLastCellNum();
                    for (short c = 0; c <= totalCells + 1; c++) {
                        XSSFCell cell = titlesRow.getCell(c);
                        if (cell == null) {
                            titles.add(ExcelHelper.EMPTY);
                            continue;
                        }
                        //titles.add(ExcelHelper.getXValue(cell).trim());
                        titles.add(ExcelHelper.getXValue(cell).replaceAll("\r|\n", "")
                                .replaceAll(" ", "").trim());
                    }
                }

                //读取Row,从第二行开始
                for (int rowNum = 1; rowNum <= totalRows; rowNum++) {
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                    if (xssfRow != null) {
                        objectMap = Maps.newHashMap();
                        totalCells = xssfRow.getLastCellNum();
                        //读取列，从第一列开始
                        for (int c = 0; c <= totalCells + 1; c++) {
                            XSSFCell cell = xssfRow.getCell(c);
                            if (cell == null) {
                                objectMap.put(titles.get(c), ExcelHelper.EMPTY);
                                continue;
                            }
                            objectMap.put(titles.get(c), ExcelHelper.getXValue(cell).trim());
                        }
                        objectMap.put("row", rowNum + 1);
                        list.add(objectMap);
                    }
                }
            }
            return list;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                input.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;

    }

    /**
     * 单元格格式
     *
     * @param hssfCell
     * @return
     */
    @SuppressWarnings({"static-access", "deprecation"})
    public static String getHValue(HSSFCell hssfCell) {
        if (hssfCell.getCellType() == CellType.BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == CellType.NUMERIC) {
            String cellValue = "";
            if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
                Date date = HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue());
                cellValue = sdf.format(date);
            } else {
                DecimalFormat df = new DecimalFormat("#.##");
                cellValue = df.format(hssfCell.getNumericCellValue());
                String strArr = cellValue.substring(cellValue.lastIndexOf(POINT) + 1, cellValue.length());
                if (strArr.equals("00")) {
                    cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
                }
            }
            return cellValue;
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

    /**
     * 单元格格式
     *
     * @param xssfCell
     * @return
     */
    public static String getXValue(XSSFCell xssfCell) {
        if (xssfCell.getCellType() == CellType.BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else if (xssfCell.getCellType() == CellType.NUMERIC) {
            String cellValue = "";
            if (ExcelHelper.isCellDateFormatted(xssfCell)) {
                Date date = ExcelHelper.getJavaDate(xssfCell.getNumericCellValue());
                cellValue = sdf.format(date);
            } else {
                DecimalFormat df = new DecimalFormat("#.##");
                cellValue = df.format(xssfCell.getNumericCellValue());
                String strArr = cellValue.substring(cellValue.lastIndexOf(POINT) + 1, cellValue.length());
                if (strArr.equals("00")) {
                    cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
                }
            }
            return cellValue;
        } else {
            return String.valueOf(xssfCell.getStringCellValue());
        }
    }

    public static boolean isCellDateFormatted(Cell cell) {
        if (cell == null) {
            return false;
        } else {
            boolean bDate = false;
            double d = cell.getNumericCellValue();
            if (isValidExcelDate(d)) {
                CellStyle style = cell.getCellStyle();
                if (style == null) {
                    return false;
                }

                int i = style.getDataFormat();
                String f = style.getDataFormatString();
                bDate = isADateFormat(i, f);
            }

            return bDate;
        }
    }

    public static boolean isCellInternalDateFormatted(Cell cell) {
        if (cell == null) {
            return false;
        } else {
            boolean bDate = false;
            double d = cell.getNumericCellValue();
            if (isValidExcelDate(d)) {
                CellStyle style = cell.getCellStyle();
                int i = style.getDataFormat();
                bDate = isInternalDateFormat(i);
            }

            return bDate;
        }
    }

    public static boolean isValidExcelDate(double value) {
        return value > -4.9E-324D;
    }

    public static Date getJavaDate(double date) {
        return DateUtil.getJavaDate(date, (TimeZone) null);
    }

    /**
     * 导出Excel xls
     *
     * @param fileName
     * @param titles
     * @param datas
     * @param response
     * @throws IOException demo:ExcelHelper.getInstance().exportExcelX("下载测试文件名"+ExcelHelper.EXCEL_SUFFIX_XLS, buildTitle(), buildData(result), response);
     */
    public void exportExcel(String fileName, List<String> titles, List<List<Object>> datas, HttpServletResponse response) throws IOException {
        Workbook workbook = new HSSFWorkbook();
        this.buildExcel(workbook, titles, datas);
        this.setExcelFileName(fileName, response);
        this.writeResponse(workbook, response);
    }

    /**
     * 导出ExcelX xlsx
     *
     * @param fileName
     * @param titles
     * @param datas
     * @param response
     * @throws IOException demo:ExcelHelper.getInstance().exportExcelX("下载测试文件名"+ExcelHelper.EXCEL_SUFFIX_XLSX, buildTitle(), buildData(result), response);
     */
    public void exportExcelX(String fileName, List<String> titles, List<List<Object>> datas, HttpServletResponse response) throws IOException {
        XSSFWorkbook workbookX = new XSSFWorkbook();
        this.buildExcel(workbookX, titles, datas);
        this.setExcelFileName(fileName, response);
        this.writeResponse(workbookX, response);
    }

    /**
     * 导出自定义的文件头的excel xls
     *
     * @param fileName
     * @param datas
     * @param response
     * @param handler
     * @throws IOException ExcelHelper.getInstance().exportExcelCustomHeader(fileName, dataList, response, sheet -> {
     *                     ExcelHelper.mergeCell(sheet, 0, 1, 0, 0);
     *                     ExcelHelper.mergeCell(sheet, 0, 1, 1, 1);
     *                     ExcelHelper.setCellValue(sheet, 0, 0, "日期");
     *                     ExcelHelper.setCellValue(sheet, 0, 1, "产品");
     *                     ExcelHelper.mergeCell(sheet, 0, 0, 7, 10);
     *                     ExcelHelper.mergeCell(sheet, 0, 0, 11, 14);
     *                     ExcelHelper.setCellValue(sheet, 0, 7, "aaa");
     *                     ExcelHelper.setCellValue(sheet, 0, 11, "bbb");
     *                     ExcelHelper.setCellValue(sheet, 1, 14, "ccc");
     *                     })
     */
    public void exportExcelCustomHeader(String fileName, List<List<Object>> datas, HttpServletResponse response, ExcelHeaderHandler handler) throws IOException {
        Workbook workbook = new HSSFWorkbook();
        this.buildExcelCustomHeader(workbook, datas, handler);
        this.setExcelFileName(fileName, response);
        this.writeResponse(workbook, response);
    }

    /**
     * 构建excel 对象workbook
     *
     * @param workbook
     * @param titles   文件头
     * @param datas    文件内容
     */
    private void buildExcel(Workbook workbook, List<String> titles, List<List<Object>> datas) {
        this.checkWorkbook(workbook);
        if (titles == null) {
            titles = Lists.newArrayList();
        }
        if (CollectionUtils.isEmpty(datas)) {
            datas = Lists.newArrayList();
        }
        int maxSheetSize = 60000;//设置excel导出分页 sheet每页6w条
        if (workbook instanceof XSSFWorkbook) {
            maxSheetSize = 1000000;
        }
        int page = datas.size() / maxSheetSize + 1;
        for (int beginPage = 0; beginPage < page; beginPage++) {
            int start = beginPage * maxSheetSize;
            int end = start + maxSheetSize;
            end = end < datas.size() ? end : datas.size();
            Sheet sheet = workbook.createSheet(DEFAULT_SHEET_NAME + (beginPage + 1));
            this.setExcelHeader(sheet, titles);
            this.setExcelRows(sheet, datas.subList(start, end));
        }
    }

    /**
     * 构建自定义的文件头的 workbook
     *
     * @param workbook
     * @param datas    文件内容
     * @param handler  自定义的文件头
     */
    private void buildExcelCustomHeader(Workbook workbook, List<List<Object>> datas, ExcelHeaderHandler handler) {
        this.checkWorkbook(workbook);
        if (CollectionUtils.isEmpty(datas)) {
            datas = Lists.newArrayList();
        }
        int maxSheetSize = 60000;//设置excel导出分页 sheet每页6w条
        int page = datas.size() / maxSheetSize + 1;
        for (int beginPage = 0; beginPage < page; beginPage++) {
            int start = beginPage * maxSheetSize;
            int end = start + maxSheetSize;
            end = end < datas.size() ? end : datas.size();
            Sheet sheet = workbook.createSheet(DEFAULT_SHEET_NAME + (beginPage + 1));
            if (handler != null) {
                handler.handler(sheet);
            }
            this.setExcelRows(sheet, datas.subList(start, end));
        }
    }

    /**
     * 自定义文件内容  .xls
     *
     * @param fileName
     * @param response
     * @param handler
     * @throws IOException
     */
    public void exportExcelCustom(String fileName, HttpServletResponse response, ExcelDataHandler handler) throws IOException {
        Workbook workbook = new HSSFWorkbook();
        this.buildExcelCustom(workbook, handler);
        this.setExcelFileName(fileName, response);
        this.writeResponse(workbook, response);
    }

    /**
     * 自定义文件内容 .xlsx
     *
     * @param fileName
     * @param response
     * @param handler
     * @throws IOException
     */
    public void exportExcelXCustom(String fileName, HttpServletResponse response, ExcelDataHandler handler) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        this.buildExcelCustom(workbook, handler);
        this.setExcelFileName(fileName, response);
        this.writeResponse(workbook, response);
    }


    public void buildExcelCustom(Workbook workbook, ExcelDataHandler handler) {
        this.checkWorkbook(workbook);
        if (handler != null) {
            handler.handler(workbook);
        }
    }

    /**
     * 文件名的定义
     * 返回response的定义
     *
     * @param fileName
     * @param response
     */
    private void setExcelFileName(String fileName, HttpServletResponse response) {
        String fn = fileName;
        try {
            fn = URLEncoder.encode(fileName, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            logger.error(e.getMessage(), e);
        }
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.setHeader("Content-Disposition", "attachment; filename=\"" + fn + "\"");
    }

    /**
     * 将文件流下载
     *
     * @param workbook
     * @param response
     * @throws IOException
     */
    private void writeResponse(Workbook workbook, HttpServletResponse response) throws IOException {
        this.checkWorkbook(workbook);
        if (response == null) {
            throw new ResponseException("无有效response");
        }
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        out.flush();
    }

    /**
     * 检测workbook 对象
     *
     * @param workbook
     */
    private void checkWorkbook(Workbook workbook) {
        if (workbook == null) {
            throw new ResponseException("无有效workbook");
        }
    }

    /**
     * 根据文件header写文件头
     *
     * @param sheet
     * @param titles
     */
    private void setExcelHeader(final Sheet sheet, final List<String> titles) {
        Row row = sheet.createRow(0);
        for (int i = 0; i < titles.size(); i++) {
            row.createCell(i).setCellValue(titles.get(i));
        }
    }

    /**
     * 文件内容的写入
     *
     * @param sheet
     * @param datas
     */
    private void setExcelRows(final Sheet sheet, final List<List<Object>> datas) {
        int lastRowNum = sheet.getLastRowNum() + 1;
        for (int j = 0; j < datas.size(); j++) {
            Row row = sheet.createRow(j + lastRowNum);
            for (int k = 0; k < datas.get(j).size(); k++) {
                Object value = datas.get(j).get(k);
                if (value != null) {
                    Cell cell = row.createCell(k);
                    ExcelHelper.setCellValue(cell, value);
                }
            }
        }
    }

    public interface ExcelHeaderHandler {
        void handler(Sheet sheet);
    }

    public interface ExcelDataHandler {
        void handler(Workbook workbook);
    }
}
